package chatserver.sql;

import chatserver.container.AutoOpInfo;
import chatserver.container.BanInfo;
import chatserver.roles.GlobalRole;
import chatserver.roles.ChannelRole;
import chatserver.logging.ServerLog;
import chatserver.objects.Channel;
import chatserver.objects.Server;
import chatserver.objects.ServerConfig;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MySqlConnection {

    private static MySqlConnection instance = null;
    private static Connection conn = null;
    private static final Object sync = new Object();
    private static String dbHost = "localhost";
    private static String dbPort = "3306";
    private static String dbDatabase = "chatserver";
    private static String dbUser = "chatserver";
    private static String dbPassword = "chatserver";
    private long lastMySqlActionTime;

    private MySqlConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            ServerLog.logError("MySQL driver not found");
        }
        rehash();
        connect();
    }

    public final void rehash() {
        Map<String, String> values = new HashMap<String, String>();

        try {
            BufferedReader br = new BufferedReader(new FileReader("ChatServerConfig.txt"));

            String line = br.readLine();
            while (line != null) {
                //ServerLog.log((line));
                if (line.length() > 0 && (line.charAt(0) != '#')) {
                    line = line.replace(" ", "");
                    line = line.replace("    ", "");
                    String[] data = line.split(":");

                    if (data.length >= 2) {
                        values.put(data[0], data[1]);
                    }
                }

                line = br.readLine();
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }

        dbHost = values.get("mysql-server") != null ? values.get("mysql-server") : dbHost;
        dbPort = values.get("mysql-server-port") != null ? values.get("mysql-server-port") : dbPort;
        dbDatabase = values.get("mysql-database") != null ? values.get("mysql-database") : dbDatabase;
        dbUser = values.get("mysql-username") != null ? values.get("mysql-username") : dbUser;
        dbPassword = values.get("mysql-password") != null ? values.get("mysql-password") : dbPassword;
        
        // ADZ - Test logs
        ServerLog.log("Host="+dbHost+"::Port="+dbPort+"::Database="+dbDatabase+"::User="+dbUser+"::Password="+dbPassword);
    }

    private boolean connect() {
        try {
            dbPassword = ""; // Test
            
            conn = DriverManager.getConnection("jdbc:mysql://" + dbHost + ":"
                    + dbPort + "/" + dbDatabase + "?" + "user=" + dbUser + "&"
                    + "password=" + dbPassword);
            
            lastMySqlActionTime = new Date().getTime();
            ServerLog.log("Connection = " + conn.toString());
        } catch (SQLException e) {
            ServerLog.logError("connection to MySQL server failed");
            return false;
        }

        return true;
    }

    private void ensureConnected() {
        try {
            if (conn.isClosed()) {
                connect();
            } else if (new Date().getTime() - lastMySqlActionTime > 30000) {
                conn.close();
                connect();
            }
        } catch (SQLException ex) {
            connect();
        }

        lastMySqlActionTime = new Date().getTime();
    }

    public static MySqlConnection getInstance() {
        if (instance == null) {
            instance = new MySqlConnection();
        }
        return instance;
    }

    public boolean isNameRegistered(String name) {
        synchronized (sync) {
            ensureConnected();

            name = name.toLowerCase();

            boolean ret = false;
            try {
                String sqlQuery = "SELECT name FROM user WHERE (name=?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, name);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    String sqlNickname = result.getString("name");
                    if (sqlNickname.equals(name)) {
                        ret = true;
                        break;
                    }
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return ret;
        }
    }

    public Map<String, String> loadChannelData(String nameId, String name, boolean isStatic, boolean isImportant, Channel.Profile profile, int slots, String topic, String topicBy, String createdBy, boolean createdByGuest) {
        synchronized (sync) {
            ensureConnected();

            Map<String, String> ret = new HashMap<String, String>();

            try {
                String sqlQuery = "SELECT * FROM channel WHERE (nameid=?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, nameId);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ResultSetMetaData mdata = result.getMetaData();
                    for (int i = 1; i <= mdata.getColumnCount(); i++) {
                        ret.put(mdata.getColumnName(i), result.getString(i));
                    }
                } else {
                    if (!createdByGuest) {
                        String sqlQuery2 = "INSERT INTO channel (nameid,name,isstatic,isimportant,profile,slots,topic,topicby,createdby) VALUES (?,?,?,?,?,?,?,?,?)";
                        PreparedStatement stmt2 = conn.prepareStatement(sqlQuery2, PreparedStatement.RETURN_GENERATED_KEYS);
                        stmt2.setString(1, nameId);
                        stmt2.setString(2, name);
                        stmt2.setInt(3, isImportant ? 1 : 0);
                        stmt2.setInt(4, isStatic ? 1 : 0);
                        stmt2.setString(5, profile.toString());
                        stmt2.setInt(6, slots);
                        stmt2.setString(7, topic);
                        stmt2.setString(8, topicBy);
                        stmt2.setString(9, createdBy);
                        stmt2.executeUpdate();
                        ResultSet rInsertId = stmt2.getGeneratedKeys();
                        rInsertId.next();
                        ret.put("id", rInsertId.getString(1));
                        stmt2.close();
                    } else {
                        ret.put("id", "-1");
                    }
                    ret.put("nameid", nameId);
                    ret.put("name", name);
                    ret.put("isstatic", Integer.valueOf(isStatic ? 1 : 0).toString());
                    ret.put("isimportant", Integer.valueOf(isImportant ? 1 : 0).toString());
                    ret.put("profile", profile.toString());
                    ret.put("slots", Integer.valueOf(slots).toString());
                    ret.put("topic", topic);
                    ret.put("topicby", topicBy);
                    ret.put("createdby", createdBy);


                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public List<String> getStaticChannels() {
        synchronized (sync) {
            ensureConnected();

            List<String> ret = new ArrayList<String>();

            try {
                String sqlQuery = "SELECT nameid FROM channel WHERE isstatic=1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    ret.add(result.getString("nameid"));
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public boolean updateChannelData(int channelId, String nameId, String name, boolean isStatic, boolean isImportant, Channel.Profile profile, int slots, String topic, String topicBy, String createdBy, String bgImageChat, String bgImageList) {
        synchronized (sync) {
            ensureConnected();

            boolean ret = false;

            try {
                String sqlQuery = "UPDATE channel SET nameid=?, name=?, isstatic=?, isimportant=?, profile=?, slots=?, topic=?, topicby=?, createdby=?, bgimagechat=?, bgimagelist=? WHERE (id=?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, nameId);
                stmt.setString(2, name);
                stmt.setInt(3, isStatic ? 1 : 0);
                stmt.setInt(4, isImportant ? 1 : 0);
                stmt.setString(5, profile.toString());
                stmt.setInt(6, slots);
                stmt.setString(7, topic);
                stmt.setString(8, topicBy);
                stmt.setString(9, createdBy);
                stmt.setString(10, bgImageChat); 
                stmt.setString(11, bgImageList);                 
                stmt.setInt(12, channelId);
                int affectedRows = stmt.executeUpdate();

                if (affectedRows > 0) {
                    ret = true;
                }

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return ret;
        }
    }

    public void killChannelData(int channelId) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "DELETE FROM channel WHERE (id=?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);;
                stmt.setInt(1, channelId);
                stmt.executeUpdate();

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void killChannelData(String channelName) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "DELETE FROM channel WHERE (name=?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);;
                stmt.setString(1, channelName);
                stmt.executeUpdate();

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public int loadChannelRoleIdOfUser(int channelId, int userId) {
        synchronized (sync) {
            ensureConnected();

            int ret = -1;

            try {
                String sqlQuery = "SELECT roleid FROM channeluserrole WHERE channelid=? AND userid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, channelId);
                stmt.setInt(2, userId);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ret = result.getInt("roleid");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            return ret;
        }
    }

    public boolean updateChannelRoleOfUser(int channelId, int userId, int roleId, String roleBy) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "UPDATE channeluserrole SET roleid=?, roleby=? WHERE channelid=? AND userid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                stmt.setString(2, roleBy);
                stmt.setInt(3, channelId);
                stmt.setInt(4, userId);
                int affectedRows = stmt.executeUpdate();

                if (affectedRows < 1) {
                    String sqlQuery2 = "INSERT INTO channeluserrole (channelid, userid, roleid, roleby) VALUES (?,?,?,?)";
                    PreparedStatement stmt2 = conn.prepareStatement(sqlQuery2);
                    stmt2.setInt(1, channelId);
                    stmt2.setInt(2, userId);
                    stmt2.setInt(3, roleId);
                    stmt2.setString(4, roleBy);
                    stmt2.executeUpdate();
                }

            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }

            return true;
        }
    }

    public boolean updateGlobalRoleOfUser(int userId, int roleId, String roleBy) {
        synchronized (sync) {
            ensureConnected();

            boolean ret = false;
            
            try {
                String sqlQuery = "UPDATE user SET roleid=?, roleby=? WHERE userid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                stmt.setString(2, roleBy);
                stmt.setInt(3, userId);
                int affectedRows = stmt.executeUpdate();

                if (affectedRows > 0) {
                    ret = true;
                }

            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }

            return ret;
        }
    }    
    
    public void killChannelRoleOfUser(int channelId, int userId) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "DELETE FROM channeluserrole WHERE channelid=? and userid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, channelId);
                stmt.setInt(2, userId);
                stmt.executeUpdate();

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void killChannelRoles(int channelId) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "DELETE FROM channeluserrole WHERE (channelid=?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);;
                stmt.setInt(1, channelId);
                stmt.executeUpdate();

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List<BanInfo> getChannelBans(int channelId) {
        synchronized (sync) {
            ensureConnected();

            List<BanInfo> ret = new ArrayList<BanInfo>();

            try {
                String sqlQuery = "SELECT name,ip,ipsize,expires,banner FROM channelban WHERE (channelid=?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, channelId);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    ret.add(
                            new BanInfo(
                            result.getString("name"),
                            result.getString("ip"),
                            result.getInt("ipsize"),
                            result.getLong("expires"),
                            result.getString("banner")));
                }

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public void addChannelBan(int channelId, BanInfo info) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "INSERT INTO channelban (channelid, name, ip, ipsize, expires, banner) VALUES (?, ?, ?, ?, ?, ?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, channelId);
                stmt.setString(2, info.name.toLowerCase());
                stmt.setString(3, info.ip);
                stmt.setInt(4, info.ipSize);
                stmt.setLong(5, info.expires);
                stmt.setString(6, info.banner);
                stmt.executeUpdate();

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void delChannelBan(int channelId, BanInfo info) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "DELETE FROM channelban WHERE channelid=? AND name=? AND ip=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, channelId);
                stmt.setString(2, info.name);
                stmt.setString(3, info.ip);
                stmt.executeUpdate();

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void delChannelBanByName(int channelId, String name) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "DELETE FROM channelban WHERE channelid=? AND name=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, channelId);
                stmt.setString(2, name);
                stmt.executeUpdate();

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List<BanInfo> getServerBans() {
        synchronized (sync) {
            ensureConnected();

            List<BanInfo> ret = new ArrayList<BanInfo>();

            try {
                String sqlQuery = "SELECT name,ip,ipsize,expires,banner FROM serverban WHERE 1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    ret.add(
                            new BanInfo(
                            result.getString("name"),
                            result.getString("ip"),
                            result.getInt("ipsize"),
                            result.getLong("expires"),
                            result.getString("banner")));
                }

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public void addServerBan(BanInfo info) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "INSERT INTO serverban (name, ip, ipsize, expires, banner) VALUES (?, ?, ?, ?, ?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, info.name.toLowerCase());
                stmt.setString(2, info.ip);
                stmt.setInt(3, info.ipSize);
                stmt.setLong(4, info.expires);
                stmt.setString(5, info.banner);
                stmt.executeUpdate();

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void delServerBan(BanInfo info) {
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "DELETE FROM serverban WHERE name=? AND ip=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, info.name.toLowerCase());
                stmt.setString(2, info.ip);
                stmt.executeUpdate();

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public boolean delServerBanByName(String name) {
        
        boolean ret = false;
        
        synchronized (sync) {
            ensureConnected();

            name = name.toLowerCase();

            try {
                String sqlQuery = "DELETE FROM serverban WHERE name=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, name);
                
                if(stmt.executeUpdate() > 0)
                {
                    ret = true;
                }


                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
        
        return ret;
    }

    public Map<String, String> loadUserData(String name) {
        synchronized (sync) {
            ServerLog.log("ADZ - MySqlConnection.java->loadUserData");
            ensureConnected();

            name = name.toLowerCase();

            Map<String, String> ret = new HashMap<String, String>();

            try {
                String sqlQuery = "SELECT id, name, roleid, roleby, age, gender, city, homepage, extra, email FROM user WHERE (name=?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, name);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ResultSetMetaData mdata = result.getMetaData();
                    for (int i = 1; i <= mdata.getColumnCount(); i++) {
                        ret.put(mdata.getColumnName(i), result.getString(i));
                        ServerLog.log("Column name="+mdata.getColumnName(i)+"::data="+result.getString(i));
                    }
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            
            return ret;
        }
    }

    public boolean registerUser(String name, String password, GlobalRole permanentRole, String permanentRoleBy, String[] userInfo, String email) {
        synchronized (sync) {
            ensureConnected();

            name = name.toLowerCase();

            boolean ret = false;

            try {
                String sqlQuery = "INSERT INTO user (name, password, roleid, roleby, age, gender, city, homepage, extra, email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);

                stmt.setString(1, name);
                stmt.setString(2, password);
                stmt.setInt(3, permanentRole.getSqlId());
                stmt.setString(4, permanentRoleBy);
                stmt.setString(5, userInfo[0]);
                stmt.setString(6, userInfo[1]);
                stmt.setString(7, userInfo[2]);
                stmt.setString(8, userInfo[3]);
                stmt.setString(9, userInfo[4]);
                stmt.setString(10, email);

                if (stmt.executeUpdate() > 0) {
                    ret = true;
                }

                //ResultSet rInsertId = stmt.getGeneratedKeys();
                //rInsertId.next();
                //int insertId = rInsertId.getInt(1);

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            return ret;
        }
    }

    public boolean changePasswordOfUser(int sqlId, String password) {
        synchronized (sync) {
            ensureConnected();

            boolean ret = false;

            try {
                String sqlQuery = "UPDATE user SET password=? WHERE id=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, password);
                stmt.setInt(2, sqlId);

                int affectedRows = stmt.executeUpdate();

                if (affectedRows > 0) {
                    ret = true;
                }

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return ret;
        }
    }

    public boolean isUserPasswordCorrect(String name, String password) {
        synchronized (sync) {
            ensureConnected();

            name = name.toLowerCase();

            boolean ret = false;

            try {
                String sqlQuery = "SELECT name FROM user WHERE name=? AND password=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, name);
                stmt.setString(2, password);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    if (result.getString("name").equals(name)) {
                        ret = true;
                    }
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return ret;
        }
    }

    public boolean isUserPasswordCorrect(String name, String digest, String digestSalt) {
        synchronized (sync) {
            ensureConnected();

            name = name.toLowerCase();

            boolean ret = false;

            try {
                String sqlQuery = "SELECT name FROM user WHERE name=? AND MD5(CONCAT(?, password))=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, name);
                stmt.setString(2, digestSalt);
                stmt.setString(3, digest);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    if (result.getString("name").equals(name)) {
                        ret = true;
                    }
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return ret;
        }
    }

    public boolean updateUserData(int userId, GlobalRole role, String roleBy, String[] userInfo, String email) {
        ServerLog.log("ADZ - MySqlConnection.java->updateUserData::userInfo");
        
        synchronized (sync) {
            ensureConnected();

            boolean ret = false;

            try {
                //String sqlQuery = "UPDATE user SET roleid=?, roleby=?, age=?, gender=?, city=?, homepage=?, extra=?, email=? WHERE (id=?)";
                int nullCount = 0;
                String sqlQuery = "UPDATE user SET roleid=?, roleby=?, ";
                sqlQuery += (!userInfo[0].equals("") && userInfo[0] != null) ? "age=?, " : "";
                sqlQuery += (!userInfo[1].equals("") && userInfo[1] != null) ? "gender=?, " : "";
                sqlQuery += (!userInfo[2].equals("") && userInfo[2] != null) ? "city=?, " : "";
                sqlQuery += (!userInfo[3].equals("") && userInfo[3] != null) ? "homepage=?, " : "";
                sqlQuery += "extra=?, email=? WHERE (id=?)";
                
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, role.getSqlId());
                stmt.setString(2, roleBy);
                if (!userInfo[0].equals(""))
                    stmt.setString(3, userInfo[0]);
                else
                    nullCount++;
                if (!userInfo[1].equals(""))
                    stmt.setString(4, userInfo[1]);
                else
                    nullCount++;
                if (!userInfo[2].equals(""))
                    stmt.setString(5, userInfo[2]);
                else
                    nullCount++;
                if (!userInfo[3].equals(""))
                    stmt.setString(6, userInfo[3]);
                else
                    nullCount++;
                
                stmt.setString(7 - nullCount, userInfo[4]);
                stmt.setString(8 - nullCount, email);
                stmt.setInt(9 - nullCount, userId);
                int affectedRows = stmt.executeUpdate();

                if (affectedRows > 0) {
                    ret = true;
                }

                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return ret;
        }
    }

    public Map<String, String> getServerConfig() {
        synchronized (sync) {
            ensureConnected();

            Map<String, String> ret = new HashMap<String, String>();

            try {
                String sqlQuery = "SELECT * FROM server WHERE 1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ResultSetMetaData mdata = result.getMetaData();
                    for (int i = 1; i <= mdata.getColumnCount(); i++) {
                        ret.put(mdata.getColumnName(i), result.getString(i));
                    }
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public List<String> getReservedNames() {
        synchronized (sync) {
            ensureConnected();

            List<String> ret = new ArrayList<String>();

            try {
                String sqlQuery = "SELECT * FROM reservedname WHERE 1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    ret.add(result.getString("name"));
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public List<String> getReservedChannels() {
        synchronized (sync) {
            ensureConnected();

            List<String> ret = new ArrayList<String>();

            try {
                String sqlQuery = "SELECT * FROM reservedchannel WHERE 1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    ret.add(result.getString("name"));
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public List<String> getAllowedHosts() {
        synchronized (sync) {
            ensureConnected();

            List<String> ret = new ArrayList<String>();

            try {
                String sqlQuery = "SELECT * FROM allowedhost WHERE 1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    ret.add(result.getString("host"));
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public List<AutoOpInfo> getServerAutoOpList() {
        synchronized (sync) {
            ensureConnected();

            List<AutoOpInfo> ret = new ArrayList<AutoOpInfo>();

            ServerConfig config = Server.getInstance().getConfig();

            try {
                String sqlQuery = "SELECT name, roleid, roleby FROM user WHERE roleid!=? AND roleid!=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, config.getDefaultGlobalRole().getSqlId());
                stmt.setInt(2, config.getDefaultRegGlobalRole().getSqlId());
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    GlobalRole role = config.findGlobalRoleById(result.getInt("roleid"));
                    String profile = role.getProfile();
                    ret.add(new AutoOpInfo(result.getString("name"), profile, result.getString("roleby")));
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public List<AutoOpInfo> getChannelAutoOpList(int channelId) {
        ServerLog.log("ADZ - MySqlConnection.java->getChannelAutoOpList:channelId="+channelId);
        synchronized (sync) {
            ensureConnected();

            List<AutoOpInfo> ret = new ArrayList<AutoOpInfo>();

            ServerConfig config = Server.getInstance().getConfig();

            if (channelId == -1) {
                return ret;
            }

            try {
                String sqlQuery = "SELECT u.name, c.roleid, c.roleby FROM channeluserrole as c, user as u WHERE c.channelid=? AND u.id=c.userid";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, channelId);

                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    ChannelRole role = config.findChannelRoleById(result.getInt("roleid"));
                    ret.add(new AutoOpInfo(result.getString("name"), role.getProfile(), result.getString("roleby")));
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public int getChannelAutoOpRoleIdOfUser(int channelId, int userId) {

        int ret = -1;

        if (channelId == -1) {
            return ret;
        }
        
        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "SELECT c.roleid FROM channeluserrole as c WHERE c.channelid=? AND c.userid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, channelId);
                stmt.setInt(2, userId);


                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ret = result.getInt("roleid");
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return -1;
            }
            return ret;
        }
    }

    public Map<String, List<Integer>> loadRightsOfChannelRole(int roleId) {
        synchronized (sync) {
            ensureConnected();

            Map<String, List<Integer>> ret = new HashMap<String, List<Integer>>();

            try {
                String sqlQuery = "SELECT c.right, c.rightappliestorole FROM channelroleright AS c WHERE roleid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    String right = result.getString("right");
                    Integer appliesToRole = result.getInt("rightappliestorole");
                    List<Integer> list;

                    if (ret.containsKey(right)) {
                        list = ret.get(right);
                    } else {
                        list = new ArrayList<Integer>();
                        ret.put(right, list);
                    }

                    if (!list.contains(appliesToRole)) {
                        list.add(appliesToRole);
                    }
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public Map<String, List<Integer>> loadRightsOfGlobalRole(int roleId) {
        synchronized (sync) {
            ensureConnected();

            Map<String, List<Integer>> ret = new HashMap<String, List<Integer>>();

            try {
                String sqlQuery = "SELECT g.right, g.rightappliestorole FROM globalroleright AS g WHERE roleid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    String right = result.getString("right");
                    Integer appliesToRole = result.getInt("rightappliestorole");
                    List<Integer> list;

                    if (ret.containsKey(right)) {
                        list = ret.get(right);
                    } else {
                        list = new ArrayList<Integer>();
                        ret.put(right, list);
                    }

                    if (!list.contains(appliesToRole)) {
                        list.add(appliesToRole);
                    }
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
            return ret;
        }
    }

    public boolean saveRightsOfGlobalRole(int roleId, Map<String, List<Integer>> rights) {
        synchronized (sync) {
            ensureConnected();

            Map<String, List<Integer>> ret = new HashMap<String, List<Integer>>();

            try {
                String sqlQuery = "DELETE FROM globalroleright WHERE roleid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                stmt.executeUpdate();
                stmt.close();

                for (Map.Entry<String, List<Integer>> entry : rights.entrySet()) {
                    for (Integer modifier : entry.getValue()) {
                        sqlQuery = "INSERT INTO globalroleright (`roleid`, `right`, `rightappliestorole`) VALUES (?, ?, ?)";
                        stmt = conn.prepareStatement(sqlQuery);
                        stmt.setInt(1, roleId);
                        stmt.setString(2, entry.getKey());
                        stmt.setInt(3, modifier);
                        stmt.executeUpdate();
                        stmt.close();
                    }
                }

            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return true;
        }
    }

    public boolean saveRightsOfChannelRole(int roleId, Map<String, List<Integer>> rights) {
        synchronized (sync) {
            ensureConnected();

            Map<String, List<Integer>> ret = new HashMap<String, List<Integer>>();

            try {
                String sqlQuery = "DELETE FROM channelroleright WHERE roleid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                stmt.executeUpdate();
                stmt.close();

                for (Map.Entry<String, List<Integer>> entry : rights.entrySet()) {
                    for (Integer modifier : entry.getValue()) {
                        sqlQuery = "INSERT INTO channelroleright (`roleid`, `right`, `rightappliestorole`) VALUES (?, ?, ?)";
                        stmt = conn.prepareStatement(sqlQuery);
                        stmt.setInt(1, roleId);
                        stmt.setString(2, entry.getKey());
                        stmt.setInt(3, modifier);
                        stmt.executeUpdate();
                        stmt.close();
                    }
                }

            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return true;
        }
    }

    public int saveGlobalRole(int roleId, String name, String profile) {
        synchronized (sync) {
            ensureConnected();

            int ret = -1;

            try {
                if (roleId == -1) {
                    String sqlQuery = "INSERT INTO globalrole (name, profile) VALUES (?, ?)";
                    PreparedStatement stmt = conn.prepareStatement(sqlQuery, PreparedStatement.RETURN_GENERATED_KEYS);
                    stmt.setString(1, name);
                    stmt.setString(2, profile);
                    stmt.executeUpdate();

                    ResultSet rInsertId = stmt.getGeneratedKeys();
                    rInsertId.next();
                    ret = rInsertId.getInt(1);

                    stmt.close();
                } else {
                    String sqlQuery = "UPDATE globalrole SET name=?, profile=? WHERE id=?";
                    PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                    stmt.setString(1, name);
                    stmt.setString(2, profile);
                    stmt.setInt(3, roleId);
                    stmt.executeUpdate();
                    stmt.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
                return ret;
            }
            return ret;
        }
    }

    public int saveChannelRole(int roleId, String name, String profile) {
        synchronized (sync) {
            ensureConnected();

            int ret = -1;

            try {
                if (roleId == -1) {
                    String sqlQuery = "INSERT INTO channelrole (name, profile) VALUES (?, ?)";
                    PreparedStatement stmt = conn.prepareStatement(sqlQuery, PreparedStatement.RETURN_GENERATED_KEYS);
                    stmt.setString(1, name);
                    stmt.setString(2, profile);
                    stmt.executeUpdate();

                    ResultSet rInsertId = stmt.getGeneratedKeys();
                    rInsertId.next();
                    ret = rInsertId.getInt(1);

                    stmt.close();
                } else {
                    String sqlQuery = "UPDATE channelrole SET name=?, profile=? WHERE id=?";
                    PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                    stmt.setString(1, name);
                    stmt.setString(2, profile);
                    stmt.setInt(3, roleId);
                    stmt.executeUpdate();
                    stmt.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
                return ret;
            }
            return ret;
        }
    }

    public List<GlobalRole> getGlobalRoleList() {

        List<GlobalRole> ret = new ArrayList<GlobalRole>();

        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "SELECT id, name, profile FROM globalrole WHERE 1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    GlobalRole grole = new GlobalRole(result.getInt("id"), result.getString("name"), result.getString("profile"));
                    ret.add(grole);
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }

        for (GlobalRole gr : ret) {
            gr.loadRights();
        }

        return ret;
    }

    public List<ChannelRole> getChannelRoleList() {

        List<ChannelRole> ret = new ArrayList<ChannelRole>();

        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "SELECT id, name, profile FROM channelrole WHERE 1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                ResultSet result = stmt.executeQuery();

                while (result.next()) {
                    ChannelRole crole = new ChannelRole(result.getInt("id"), result.getString("name"), result.getString("profile"));
                    ret.add(crole);
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }

        for (ChannelRole cr : ret) {
            cr.loadRights();
        }

        return ret;
    }

    public boolean isGlobalRoleInUse(int sqlId) {

        if (sqlId == -1) {
            return false;
        }

        synchronized (sync) {
            ensureConnected();

            boolean ret = false;

            try {
                String sqlQuery = "SELECT * FROM user WHERE roleid=? LIMIT 1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, sqlId);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ret = true;
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return ret;
        }
    }

    public boolean isChannelRoleInUse(int sqlId) {

        if (sqlId == -1) {
            return false;
        }

        synchronized (sync) {
            ensureConnected();

            boolean ret = false;

            try {
                String sqlQuery = "SELECT * FROM channeluserrole WHERE roleid=? LIMIT 1";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, sqlId);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ret = true;
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return ret;
        }
    }

    public boolean delGlobalRole(int roleId) {

        if (roleId == -1) {
            return false;
        }

        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "DELETE FROM globalroleright WHERE roleid=? OR rightappliestorole=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                stmt.setInt(2, roleId);
                stmt.executeUpdate();
                stmt.close();

                sqlQuery = "DELETE FROM globalrole WHERE id=?";
                stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                stmt.executeUpdate();
                stmt.close();

            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return true;
        }
    }

    public boolean delChannelRole(int roleId) {

        if (roleId == -1) {
            return false;
        }

        synchronized (sync) {
            ensureConnected();

            try {
                String sqlQuery = "DELETE FROM channelroleright WHERE roleid=? OR rightappliestorole=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                stmt.setInt(2, roleId);
                stmt.executeUpdate();
                stmt.close();

                sqlQuery = "DELETE FROM channelrole WHERE id=?";
                stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, roleId);
                stmt.executeUpdate();
                stmt.close();

            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
            return true;
        }
    }

    public int getGlobalRoleIdOfUser(String name) {
        name = name.toLowerCase();

        synchronized (sync) {
            ensureConnected();

            int ret = -1;

            try {
                String sqlQuery = "SELECT roleid FROM user WHERE name=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, name);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ret = result.getInt(1);
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return ret;
            }
            return ret;
        }
    }

    public int getUserIdOfUser(String name) {
        name = name.toLowerCase();

        synchronized (sync) {
            ensureConnected();

            int ret = -1;

            try {
                String sqlQuery = "SELECT id FROM user WHERE name=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setString(1, name);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ret = result.getInt(1);
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return ret;
            }
            return ret;
        }
    }

    public int getChannelRoleIdOfUser(int channelId, String name) {
        int userId = getUserIdOfUser(name);
        if (userId == -1) {
            return -1;
        }

        synchronized (sync) {
            ensureConnected();

            int ret = -1;

            try {
                String sqlQuery = "SELECT roleid FROM channeluserrole WHERE channelid=? AND userid=?";
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setInt(1, channelId);
                stmt.setInt(2, userId);
                ResultSet result = stmt.executeQuery();

                if (result.next()) {
                    ret = result.getInt(1);
                }
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                return ret;
            }
            return ret;
        }
    }
}
